# !/bin/bash

if [ -n "$1" ]; then
    data_date=$1
else
  data_date=$(date -d "-1 days" +%F)
fi


tmp_event_log_clean="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_clean;
CREATE TABLE IF NOT EXISTS jtp_app_tmp.tmp_event_log_clean
AS SELECT
         account,
         appid,
         appversion,
         carrier,
         deviceid,
         devicetype,
         eventid,
         ip,
         latitude,
         longitude,
         nettype,
         osname,
         osversion,
         properties,
         releasechannel,
         resolution,
         sessionid,
         \`timestamp\`,
         dt
FROM jtp_app_ods.ods_event_app_log
WHERE dt='${data_date}'
AND deviceid IS NOT NULL AND LENGTH(deviceid)!=0
AND eventid IS NOT NULL AND LENGTH(eventid)!=0
AND sessionid IS NOT NULL AND LENGTH(sessionid)!=0
AND properties IS NOT NULL AND SIZE(properties)!=0;
"

tmp_event_log_session="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_session;
create table if not exists jtp_app_tmp.tmp_event_log_session
as
select
    account, appid, appversion, carrier, deviceid, devicetype, eventid
     , ip, latitude, longitude, nettype, osname, osversion, properties
     , releasechannel, resolution, sessionid, \`timestamp\`, dt,
    last_value(start_session_id,TRUE) over (partition by sessionid order by \`timestamp\`) as new_session_id
from (
         select
             *,
             if(diff_ts/1000/60 >=10,concat(sessionid,'-',\`timestamp\`),NULL) as start_session_id
         from (
                  SELECT
                      *,
                      (\`timestamp\`-lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\`)) as diff_ts
                  FROM jtp_app_tmp.tmp_event_log_clean
              ));"


tmp_event_log_area="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_area;
CREATE TABLE jtp_app_tmp.tmp_event_log_area
AS
SELECT
    t1.*
     , t2.province, t2.city, t2.district, t2.street
FROM jtp_app_tmp.tmp_event_log_session t1
         LEFT JOIN jtp_app_dim.dim_md_areas t2 ON cottt(t1.latitude, t1.longitude, 6) = t2.geo_hash
where t2.dt='${data_date}';"


dwd_event_log_detail="
INSERT OVERWRITE TABLE jtp_app_dwd.dwd_event_log_detail PARTITION (dt = '${data_date}')
SELECT
    account, appid, appversion, carrier, deviceid, devicetype, eventid
     , ip, latitude, longitude, province, city, district, nettype
     , osname, osversion, properties, releasechannel, resolution, new_session_id, \`timestamp\`
FROM jtp_app_tmp.tmp_event_log_area
;"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${tmp_event_log_clean}"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${tmp_event_log_session}"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${tmp_event_log_area}"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${dwd_event_log_detail}"
